package emp.dao;

import emp.config.DataSourceConfig;
import emp.config.PaginationService;
import emp.dao.rowmapper.EmployeeRowMapper;
import emp.entity.Department;
import emp.entity.Employee;
import emp.entity.PageInfo;
import emp.entity.SearchCriteria;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

/**
 * @author 叶枫
 * @date 2024/12/18.
 */


public class EmployeeDao {

    private static DataSourceConfig dataSourceConfig;

    private JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceConfig.dataSource());


    public PageInfo<Employee> listByPageSearch(SearchCriteria searchCriteria){
        PaginationService paginationService = new PaginationService();
        String sql = "SELECT e.emp_id, e.emp_name, e.gender, e.age, e.salary, e.address, " +
                "e.hometown, e.phone, e.dept_id, edu.edu_id, edu.edu_level, " +
                "dept.dept_name " +
                "FROM Employee e " +
                "JOIN Education edu ON e.edu_id = edu.edu_id " +
                "JOIN Department dept ON e.dept_id = dept.dept_id " +
                "where 1=1";
        Integer pageSize = searchCriteria.getPageSize();
        Integer pageNumber = searchCriteria.getPageNumber();
      return   paginationService.getPage(sql,searchCriteria,new EmployeeRowMapper(),pageNumber,pageSize);
    }

    //添加
    public boolean add(Employee employee){
        String sql = "INSERT INTO employee(emp_name,gender,age,edu_id,salary,address,hometown,phone,dept_id)VALUES(?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,employee.getEmpName(),employee.getGender(),employee.getAge(),employee.getEducation().getEduId(),employee.getSalary(),employee.getAddress(),employee.getHometown(),employee.getPhone(),employee.getDeptId()) > 0;
    }
    //删除
    public boolean delete(int empId){
        String sql = "DELETE FROM employee WHERE emp_id = ?";
        return jdbcTemplate.update(sql,empId) > 0;
    }

    public boolean update(Employee employee) {
        // 基础 SQL 语句
        StringBuilder sqlBuilder = new StringBuilder("UPDATE employees SET ");
        List<Object> params = new ArrayList<>();

        // 动态拼接字段和参数
        if (employee.getEmpName() != null) {
            sqlBuilder.append("emp_name = ?, ");
            params.add(employee.getEmpName());
        }

            sqlBuilder.append("gender = ?, ");
            params.add(employee.getGender());


            sqlBuilder.append("salary = ?, ");
            params.add(employee.getSalary());

        if (employee.getAddress() != null) {
            sqlBuilder.append("address = ?, ");
            params.add(employee.getAddress());
        }
        if (employee.getHometown() != null) {
            sqlBuilder.append("hometown = ?, ");
            params.add(employee.getHometown());
        }
        if (employee.getPhone() != null) {
            sqlBuilder.append("phone = ?, ");
            params.add(employee.getPhone());
        }

            sqlBuilder.append("dept_id = ?, ");
            params.add(employee.getDeptId());


        // 移除最后一个多余的逗号和空格
        if (sqlBuilder.length() > 0) {
            sqlBuilder.setLength(sqlBuilder.length() - 2);
        }

        // 添加 WHERE 条件，确保只更新特定的员工
        sqlBuilder.append(" WHERE emp_id = ?");
        params.add(employee.getEmpId());

        // 执行更新操作
        return jdbcTemplate.update(sqlBuilder.toString(), params.toArray()) > 0;
    }

    //根据id查询员工
    public Employee findById(int empId){
        String sql = "SELECT emp_id, emp_name,gender,salary,address,hometown,phone,dept_id FROM employee WHERE emp_id = ?";
        Object forObject = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Employee.class), empId);
        return (Employee)forObject;
    }


    public Integer getCount() {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM employee", Integer.class);
    }
}
